package com.sptokentest.config;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DerbyClient {
    private static final String DB_URL = "jdbc:derby:dexterityData;create=true";
    private Connection conn;
    private Statement stmt;



    public DerbyClient() throws SQLException {
        conn = DriverManager.getConnection(DB_URL);
        stmt = conn.createStatement();
    }

    /**
     * 查询该表所有的信息
     */
    public ResultSet select(String tableName) throws SQLException {
        return stmt.executeQuery("SELECT * FROM " + tableName);
    }

    /**
     * 关闭客户端
     */
    public void close() throws SQLException {
        stmt.close();
        conn.close();
    }

    /**
     * 创建表
     */
    public void createTable(String tableName, String... columns) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE ").append(tableName).append("(");
        for (int i = 0; i < columns.length; i++) {
            sb.append(columns[i]);
            if (i < columns.length - 1) {
                sb.append(",");
            }
        }
        sb.append(")");
        stmt.executeUpdate(sb.toString());
    }

    /**
     * 查询库中的所有表信息
     * @throws SQLException
     */
    public void listTable() throws SQLException {
        String sql = "SELECT TABLENAME FROM SYS.SYSTABLES WHERE TABLETYPE='T'";
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String tableName = rs.getString("TABLENAME");
            System.out.println(tableName);
        }
    }

    /**
     * 删除表
     */
    public void deleteTable(String tableName) throws SQLException{
        String sql = "DROP TABLE " + tableName;
        stmt.executeUpdate(sql);
    }

    /**
     * 创建初始表结构
     * @throws SQLException
     */
    public void createInitTable() throws SQLException{
        createTable(
                "sys_user",
                "id BIGINT PRIMARY KEY",
                "username VARCHAR(255)",
                "password VARCHAR(255)",
                "status INT",
                "name VARCHAR(255)",
                "create_by BIGINT",
                "create_time VARCHAR(30)");
        createTable(
                "sys_user_role",
                "user_id BIGINT PRIMARY KEY",
                "role_id BIGINT");
        createTable(
                "sys_role",
                "role_id BIGINT PRIMARY KEY",
                "role_name VARCHAR(255)",
                "per VARCHAR(255)",
                "status INT",
                "create_time VARCHAR(30)",
                "update_time VARCHAR(30)");
        createTable(
                "sys_role_menu",
                "rmid BIGINT PRIMARY KEY",
                "role_id BIGINT",
                "menu_id BIGINT");
        createTable(
                "sys_menu",
                "menu_id BIGINT PRIMARY KEY",
                "menu_name VARCHAR(255)",
                "is_enabled INT",
                "description VARCHAR(255)",
                "perms VARCHAR(255)",
                "route VARCHAR(255)",
                "icon VARCHAR(255)",
                "mid BIGINT");
    }


    /**
     * 插入初始表数据
     */
    public void insertInitData() throws SQLException{
        List<String> sqlList = new ArrayList<>();
        String sql1 = "INSERT INTO sys_user VALUES (1,'admin','123456',1,'许佳豪',1,'2023-02-08 22:02:16')";
        String sql39 = "INSERT INTO sys_user VALUES (2,'xujiahao','123456',1,'许佳豪',1,'2023-02-08 22:02:16')";

        String sql2 = "INSERT INTO sys_user_role VALUES (1,1)";
        String sql40 = "INSERT INTO sys_user_role VALUES (2,1)";


        String sql3 = "INSERT INTO sys_role VALUES (1,'超级管理员','superadmin',1,'2023-02-08 22:02:16','2023-02-27 11:31:02')";

        String sql4 = "INSERT INTO sys_menu VALUES (2,'存储桶列表',1,null,'system:bucket:list','/BucketList','cloud-download',null)";
        String sql5 = "INSERT INTO sys_menu VALUES (3,'使用统计',1,null,'system:statistic:list','/usagestatistics','dashboard',null)";
        String sql6 = "INSERT INTO sys_menu VALUES (4,'智能工具箱',1,null,'system:intellengence:list','/intelligenttoolbox','control-platform',null)";
        String sql7 = "INSERT INTO sys_menu VALUES (5,'用户管理',1,null,'system:user:list','/usermanage','user',null)";
        String sql8 = "INSERT INTO sys_menu VALUES (6,'角色管理',1,null,'system:role:list','/rolemanage','usergroup',null)";
        String sql9 = "INSERT INTO sys_menu VALUES (7,'菜单管理',1,null,'system:menu:list','/menumanage','logo-windows-filled',null)";
        String sql10 = "INSERT INTO sys_menu VALUES (9,'添加用户',1,null,'system:user:add',null,null,5)";
        String sql11 = "INSERT INTO sys_menu VALUES (10,'编辑用户',1,null,'system:user:edit',null,null,5)";
        String sql12 = "INSERT INTO sys_menu VALUES (11,'删除用户',1,null,'system:user:delete',null,null,5)";
        String sql13 = "INSERT INTO sys_menu VALUES (12,'用户状态',1,null,'system:user:status',null,null,5)";
        String sql14 = "INSERT INTO sys_menu VALUES (13,'添加角色',1,null,'system:role:add',null,null,6)";
        String sql15 = "INSERT INTO sys_menu VALUES (14,'编辑角色',1,null,'system:role:edit',null,null,6)";
        String sql16 = "INSERT INTO sys_menu VALUES (15,'删除角色',1,null,'system:role:delete',null,null,6)";
        String sql17 = "INSERT INTO sys_menu VALUES (16,'角色状态',1,null,'system:role:status',null,null,6)";
        String sql18 = "INSERT INTO sys_menu VALUES (17,'查询用户',1,null,'system:user:query',null,null,5)";
        String sql19 = "INSERT INTO sys_menu VALUES (18,'查询角色',1,null,'system:role:query',null,null,6)";
        String sql20 = "INSERT INTO sys_menu VALUES (19,'查询菜单',1,null,'system:menu:query',null,null,7)";
        String sql21 = "INSERT INTO sys_menu VALUES (20,'编辑菜单',1,null,'system:menu:edit',null,null,7)";
        String sql22 = "INSERT INTO sys_menu VALUES (21,'删除菜单',1,null,'system:menu:delete',null,null,7)";
        String sql23 = "INSERT INTO sys_menu VALUES (22,'菜单状态',1,null,'system:menu:status',null,null,7)";
        String sql24 = "INSERT INTO sys_menu VALUES (23,'添加菜单',1,null,'system:menu:add',null,null,7)";
        String sql25 = "INSERT INTO sys_menu VALUES (24,'创建存储桶',1,null,'system:bucket:add',null,null,2)";
        String sql26 = "INSERT INTO sys_menu VALUES (25,'删除存储桶',1,null,'system:bucket:delete',null,null,2)";
        String sql27 = "INSERT INTO sys_menu VALUES (26,'编辑存储桶',1,null,'system:bucket:edit',null,null,2)";
        String sql28 = "INSERT INTO sys_menu VALUES (27,'停用存储桶',1,null,'system:bucket:status',null,null,2)";
        String sql29 = "INSERT INTO sys_menu VALUES (28,'查询存储桶',1,null,'system:bucket:query',null,null,2)";
        String sql30 = "INSERT INTO sys_menu VALUES (29,'图片编辑',1,null,'system:intellengence:photo:edit',null,null,4)";
        String sql31 = "INSERT INTO sys_menu VALUES (30,'图片水印',1,null,'system:intellengence:photo:watermark',null,null,4)";
        String sql32 = "INSERT INTO sys_menu VALUES (31,'智能抠图',1,null,'system:intellengence:photo:Matting',null,null,4)";
        String sql33 = "INSERT INTO sys_menu VALUES (32,'视频编辑',1,null,'system:intellengence:video:edit',null,null,4)";
        String sql34 = "INSERT INTO sys_menu VALUES (33,'老片修复',1,null,'system:intellengence:photo:repair',null,null,4)";
        String sql35 = "INSERT INTO sys_menu VALUES (34,'统计信息',1,null,'system:statistic:message',null,null,3)";

        String sql36 = "INSERT INTO sys_role_menu VALUES (1,1,18)";
        String sql37 = "INSERT INTO sys_role_menu VALUES (2,1,14)";
        String sql38 = "INSERT INTO sys_role_menu VALUES (3,1,6)";

        sqlList.add(sql1);
        sqlList.add(sql2);
        sqlList.add(sql3);
        sqlList.add(sql4);
        sqlList.add(sql5);
        sqlList.add(sql6);
        sqlList.add(sql7);
        sqlList.add(sql8);
        sqlList.add(sql9);
        sqlList.add(sql10);
        sqlList.add(sql11);
        sqlList.add(sql12);
        sqlList.add(sql13);
        sqlList.add(sql14);
        sqlList.add(sql15);
        sqlList.add(sql16);
        sqlList.add(sql17);
        sqlList.add(sql18);
        sqlList.add(sql19);
        sqlList.add(sql20);
        sqlList.add(sql21);
        sqlList.add(sql22);
        sqlList.add(sql23);
        sqlList.add(sql24);
        sqlList.add(sql25);
        sqlList.add(sql26);
        sqlList.add(sql27);
        sqlList.add(sql28);
        sqlList.add(sql29);
        sqlList.add(sql30);
        sqlList.add(sql31);
        sqlList.add(sql32);
        sqlList.add(sql33);
        sqlList.add(sql34);
        sqlList.add(sql35);
        sqlList.add(sql36);
        sqlList.add(sql37);
        sqlList.add(sql38);
        sqlList.add(sql39);
        sqlList.add(sql40);

        Statement stmt=null;
        for (String item :
                sqlList) {
            stmt = conn.createStatement();
            stmt.executeUpdate(item);
        }
    }

    /**
     * 初始化数据表
     * @throws SQLException
     */
    public void init() throws SQLException  {
        //删除表
        deleteTable("sys_user");
        deleteTable("sys_user_role");
        deleteTable("sys_role");
        deleteTable("sys_role_menu");
        deleteTable("sys_menu");
        //创建表
        createTable(
                "sys_user",
                "id BIGINT PRIMARY KEY",
                "username VARCHAR(255)",
                "password VARCHAR(255)",
                "status INT",
                "name VARCHAR(255)",
                "create_by BIGINT",
                "create_time VARCHAR(30)");
        createTable(
                "sys_user_role",
                "user_id BIGINT PRIMARY KEY",
                "role_id BIGINT");
        createTable(
                "sys_role",
                "role_id BIGINT PRIMARY KEY",
                "role_name VARCHAR(255)",
                "per VARCHAR(255)",
                "status INT",
                "create_time VARCHAR(30)",
                "update_time VARCHAR(30)");
        createTable(
                "sys_role_menu",
                "rmid BIGINT PRIMARY KEY",
                "role_id BIGINT",
                "menu_id BIGINT");
        createTable(
                "sys_menu",
                "menu_id BIGINT PRIMARY KEY",
                "menu_name VARCHAR(255)",
                "is_enabled INT",
                "description VARCHAR(255)",
                "perms VARCHAR(255)",
                "route VARCHAR(255)",
                "icon VARCHAR(255)",
                "mid BIGINT");
    }


    public static void main(String[] args) throws SQLException {
        DerbyClient client = new DerbyClient();
        client.init();
        client.listTable();
        client.insertInitData();
        client.close();
    }
}
